#title: 自定义 SQL
#index:0,1
----------------------------------------------------------------------------------------------------
Nutz.Dao 自定义 SQL 的概述

	Nutz.Dao 提供了大多数简单的操作，在80%以上的情况下，你并不需要编写 SQL，因为 Nutz.Dao 会自动替你
	生成可以使用的 SQL。但是，在某些特殊的情况下，尤其是考虑到效率等问题，直接写作 SQL 仍然是程序员们
	的一个杀手锏，有了这个杀手锏，程序员们永远可以针对任何数据库做他们想要的任何操作。

	在之前的时代，很多程序员将 SQL 代码同 Java 代码混杂在一起，即所谓的{#00A;*硬编码}。硬编码通常是不
	好的，所以很多程序员都采用了各种办法将 SQL 提炼出来存放在一个独立的文件中。其中比较著名的一个框架
	就是 iBatis。这个小巧的 SQL 映射框架（Nutz.Dao 比它还小）在这个领域里干的不错。缺省的它将 SQL 存
	放在 XML 文件中，现在最新的 iBatis3 也提供了JAVA注解的写法。但是我并不认为 XML 文件或者是 JAVA
	注解是存放我的 SQL 语句好地方，我认为 SQL 存放的地方，应该是可以用 Eclipse 的 SQL 编辑器打开并且
	能够被正确语法高亮的一种文本文件。
	
	著名的 Hibernate 提供 HQL， 虽然语法近似于 SQL 但是它必然会有两个不可避免的缺点
	 # 对于数据库方言支持的不好
	 # 必然会增加使用者的学习曲线

	因此，Nutz.Dao 的自定义 SQL 部分的解决方案是：
	 # 用户可以硬编码 SQL 语句，比如：
		{{{<JAVA>
		Sql sql = Sqls.create("DELETE FROM t_abc WHERE name='Peter'");
		}}}
	 # 支持占位符的书写方式，比如：
		{{{<JAVA>
		Sql sql = Sqls.create("DELETE FROM $table WHERE name=@name");
		sql.vars().set("table","t_abc");
		sql.params().set("name","Peter");
		// 连写
		sql.setVar("table","t_abc").setVar(...);
		sql.setParam("name","Peter").setParam(...);
		}}}
		 * $table 将会被替换成 {#F0F;t_abc}
		 * @name 将会被替换成 {#F0F;?}，用来创建 PreparedStatement
	 # 用户可以将所有的 SQL 语句存放在一个或者多个文件中，语句的间隔可以通过注释，比如：
		{{{
		/* delete.data */
		DELETE FROM $table WHERE name LIKE @name
		/* update.data */
		UPDATE FROM $table SET name=@name WHERE id=@id
		}}}
		在你的 Java 代码中：
		{{{
		Sql sql = dao.sqls().create("delete.data");
		}}}
	 # 你可以为你的 SQL 任意定制回调，后面会有详细讲解

	下面我们就由 org.nutz.dao.sql.Sql 接口入手，详细讲解一下 Nutz.Dao 的自定义 SQL 解决方案
----------------------------------------------------------------------------------------------------
Sql 对象 -- org.nutz.dao.sql.Sql
	
	我几乎是不加思索的将 SQL 的实现封装在一个接口后面。现在想想这到也没什么坏处。接口的默认实现是
	{#080;* org.nutz.dao.impl.sql.NutSql}。你可以直接 new 这个对象，当然，我也提供了构造 Sql 对象的
    静态方法:

	如何创建 Sql 对象
		通过 org.nutz.dao.Sqls 类提供的静态方法 create，你可以很方便的构建你的 Sql 对象
		{{{<JAVA>
		Sql sql = Sqls.create("INSERT INTO t_abc (name,age) VALUES('Peter',18)");
		}}}
		Sqls 提供的 
         * fetchEntity
         * fetchInt
         * fetchString
         * fetchRecord
         * queryEntity 
         * queryRecord
         * 其他等你发现
        方法来帮助你构建 Sql 对象。它们之间的区别在稍后会详细说明。
		
		通常的情况，你需要构建某些 {*动态} 的 SQL，所以我也允许你为你的 SQL 设置占位符，占位符分两种：
		 * 变量(var)占位符 - 形式为 {#080;$名称}
			 * 以字符 $ 开头，名称为英文字母，数字，下划线，减号和句点。
			 * 正则表达式为： 
				 {{{
				 [$][a-zA-Z0-9_-]
				 }}}
			 * 在执行 SQL 前，该占位符会被用户设置的值替换
			 * 类似 C 语言中的 {#080;*宏}
		 
		 * 参数(param)占位符 - 形式为 {#080;@名称}
			 * 以字符 @ 开头，名称为英文字母，数字，下划线，减号和句点。
			 * 正则表达式为：
				{{{
				[@][a-zA-Z0-9_-]+
				}}}
			 * 在执行 SQL 前，该占位符会被字符 "{#F0F;*?}" 替换，用来创建 PreparedStatement
			 * Nutz.Dao 会自动计算 PreparedStatement的索引值
		
		所有的占位符可以同样的名称出现的多个地方。并且变量占位符和参数占位符的名称不互相干扰，比如:
		{{{<JAVA>
		Sql sql = Sqls.create("INSERT INTO $table ($name,$age,$weight) VALUES(@name,@age,@weight)");
		// 为变量占位符设值
		sql.vars().set("table","t_person");	
		sql.vars().set("name","f_name").set("age","f_age").set("weight","f_weight");
		// 为参数占位符设值
		sql.params().set("name","Peter").set("age",18).set("weight",60);
		}}}
		通过上例，我们可以看出，变量占位符和参数占位符的确可以重名且不相互干扰的。

    Sql 的逃逸字符
        
        有些时候，有的朋友给出的 SQL 包括特殊字符 '@' 或者 '$'，比如:
        {{{
        Sql sql = Sqls.create("INSERT INTO t_usr (name,email) VALUES('XiaoMing','xiaoming@163.com');"
        }}}
        这个时候，因为有关键字 '@'，所以 SQL 不能被正确解析，因为你的本意是给一个 'xiaoming@163.com' 
        这个字符串。但是 Nutz.Dao 却认为这个是个语句参数。
        
        这时候你可以使用逃逸字符:
        {{{
        Sql sql = Sqls.create("INSERT INTO t_usr (name,email) VALUES('XiaoMing','xiaoming@@163.com');"
        }}}
        即
         * 输入 "@@" 表示一个 '@'
         * 输入 "$$$$" 表示一个 '$'
	
	如何执行 Sql 对象
		当你顺利的创建了一个 Sql 对象，执行它就相当简单了，比如：
		{{{<JAVA>
		public void demoSql(Dao dao){
			Sql sql = Sqls.create("SELECT name FROM t_abc WHERE name LIKE @name");
			sql.params().set("name", "A%");
			dao.execute(sql);
		}
		}}}
		这就完了吗？我怎么取得查询的结果呢。是的，同 UPDATE, DELETE, INSERT 不同， SELECT 是需要返回
		结果的，但是 Nutz.Dao 也不太清楚怎样为你自定义的 SELECT 语句返回结果，于是，就需要你设置回调。

	回调的用处
		接上例，你需要这么改造一下你的函数：
		{{{<JAVA>
		List<String> demoSql(Dao dao) {
			Sql sql = Sqls.create("SELECT name FROM t_abc WHERE name LIKE @name");
			sql.params().set("name", "A%");
			sql.setCallback(new SqlCallback() {
				public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
					List<String> list = new LinkedList<String>();
					while (rs.next())
						list.add(rs.getString("name"));
					return list;
				}
			});
			dao.execute(sql);
			return sql.getList(String.class);
			// Nutz内置了大量回调, 请查看Sqls.callback的属性
		}
		}}}
		看到熟悉的 ResultSet 了吧。 当然，如果你执行的不是 SELECT 语句，你依然可以设置回调，但是 ResultSet 
		参数就是 null了。
		
		总结一下：
		 # 回调对象实现接口 org.nutz.dao.sql.SqlCallback，事实上，就像上例所示，这种场景非常适合使用匿名类。
		 # 你的回调函数的返回值会存放在 Sql 对象中
		 # 调用 sql.getResult() 可以直接返回这个对象
		 # sql.getList() 以及 sql.getObject() 方法会泛型安全的替你转型
			 * 如果你的对象类型符合要求，则直接返回，否则会通过 [../lang/castors.man Nutz.Castors] 替你转换。
			 * 对于 getList()，泛型参数用来描述集合内部元素的类型
		 # sql.getInt() 会安全的替你将结果转成 int，如果它可以被转成 int 的话，以下是我能想到的列表：
			 * 字符串
			 * 各种数字类型
			 * 字符
			 * 布尔类型
			 
    获取一个列表的回调
        
        为了更加详细的说明一下回调的用处，我们再下面这个例子：
        {{{<Java>
        Sql sql = Sqls.create("SELECT m.* FROM master m JOIN detail d ON m.d_id=d.d_id WHERE d.name='aa'");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(Master.class));
        dao.execute(sql);
        List<Master> list = sql.getList(Master.class);
        }}}
        只要你保证你的 Master 类声明了 `@Table` 并且每个字段上的 `@Column` 可以同你的 ResultSet 配置起来
        那么，上面的代码可以很方便的帮你获取一个 `List<Master>`.

	
	
    批量执行
        在 Nutz 1.b.38 之后的版本，自定义 SQL 可以支持批量操作
        {{{
        Sql sql = Sqls.create("UPDATE t_pet SET name=@name WHERE id=@id");
        sql.params().set("name","XiaoBai").set("id",4);
        sql.addBatch();
        sql.params().set("name","XiaoHei").set("id",5);
        sql.addBatch();
        dao.execute(sql);
        }}}
----------------------------------------------------------------------------------------------------
Nutz.Dao SQL 文件的格式

	我们了解了如何构建 Sql 对象，但是一个应用通常由很多 SQL 语句构成，如何管理这些语句呢？前面我说过，我希望：
	" {#00A;用户可以将所有的 SQL 语句存放在一个或者多个文件中，语句的间隔可以通过注释} "。
	是的这是一种非常简单的纯文本文件，文件里只包含三种信息：
	
	 # SQL 语句
	 # SQL 语句的名称 {#777;_(或者说是键值)}。你的程序可以通过语句的名称获取到某一条或几条 SQL 语句
	 # 注释  (通常包括在 /* 与 */ 之间)
	
	{#F00;*请注意：} 你的 SQL 文件必须为 "{#F0F;*UTF-8}" 编码。
	
	下面是一个例子
	{{{<SQLs>
	/*
	这里是这个 SQL 文件的注释，你随便怎么写
	*/
	/* sql1 */
	DROP TABLE t_abc
	/* 你可以随便写任何的注释文字，只有距离 SQL 语句最近的那一行注释，才会被认为是键值 */
	/* getpet*/
	SELECT * FROM t_pet WHERE id=@id
	/* listpets*/
	SELECT * FROM t_pet $condition
	}}}
		
----------------------------------------------------------------------------------------------------
加载 SQL 文件

	如何使用上述的 SQL 文件呢，可以将数个 SQL 文件加载到 Dao 对象中。在之后，只要得到 Dao 的对象，可以使用 dao.sqls() 方法获得
	org.nutz.dao.SqlManager 接口，从这个接口中你就可以获得你预先定义好的 Sql 对象了。
	
	对于 Dao 接口的默认实现， org.nutz.dao.impl.NutDao，提供两个方法，一个是通过构造函数，另一个是 setter 函数。

	在构造时加载(示意代码,新建NutDao属于重量级操作,应使用单例或Ioc模式)
		{{{<JAVA>
		Dao dao = new NutDao(datasource,new FileSqlManager("demo/sqls/all.sqls"));
		System.out.println(dao.sqls().count());
		}}}
		上述代码将打印出 all.sqls 文件中 SQL 语句的数量。
		路径 "demo/sqls/all.sqls" 是一个存在在 CLASSPATH 的文件。
		
		 * FileSqlManager 的构造函数接受数目可变的字符串对象，每个对象就是 SQL 文件的路径。
		 * 如不是存在在 CLASSPATH 中的文件，则需要写上绝对路径。
		 * 如果你给出的 path 是一个目录，那么该目录下所有后缀为{*.sqls} 的文件都会被加载

	在构造之后的任何时加载(示意代码,新建NutDao属于重量级操作,应使用单例或Ioc模式)
		{{{<JAVA>
		Dao dao = new NutDao(datasource);
		((NutDao)dao).setSqlManager(new FileSqlManager("demo/sqls/all.sqls"));
		System.out.println(dao.sqls().count());
		}}}

------------------------------------------------------------------------------------------------
条件变量占位符
	我认为 Nutz.Dao 比较吸引人的一个函数就是 Dao.query，它允许你用多种方法传入一个条件
	关于复杂的条件，请参看 [condition.man 复杂的SQL条件]
	
	在 Sql 对象中，我在接口里也设计了一个方法 :
	{{{<JAVA>
	Sql setCondition(Condition condition);
	}}}
	是的，你的 Sql 对象也可以使用 Condition，但是这个 Condition 要如何同你自定义的 SQL 拼装在一起呢，
	这里，我提供了一个特殊的变量占位符 -- 条件变量占位符 $condition
	--------------------------------------------------------------------------------------------
	特殊的占位符 -- {#00A;*${condition}}
		唯一需要说明的是，在你写作的 SQL 中，需要声明一个特殊的占位符，比如下面的代码输出所有 id 大
		于 35 的 Pet 对象的名称
		{{{<JAVA>
		Sql sql = Sqls.create("SELECT name FROM t_pet $condition");
		sql.setCondition(Cnd.where("id", ">", 35)).setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<String> list = new LinkedList<String>();
				while (rs.next())
					list.add(rs.getString("name"));
				return list;
			}
		});
		dao.execute(sql);
		for (String name : sql.getList(String.class))
			System.out.println(name);
		}}}
		请主要看看这两行代码：
		{{{<JAVA>
		Sql sql = Sqls.create("SELECT name FROM t_pet $condition");
		sql.setCondition(Cnd.where("id", ">", 35));
		}}}
		第一行的占位符 `$condition` 已经被 Nutz.Dao 保留。声明了该占位符的 SQL 都可以使用 setCondition 函数。
		否则，你设置的条件将无效。

        另外一个例子 - {*将所有的 id 大于 35 的 Pet 对象的 masterId 设置为 45}
        {{{<JAVA>
        void demoCondition2(Dao dao){
            Sql sql = Sqls.create("UPDATE t_pet SET masterid=@masterId $condition");
            sql.params().set("masterId", 45);
            sql.setCondition(Cnd.wrap("id>35"));
            dao.execute(sql);
        }
        }}}
	---------------------------------------------------------------------------------------------------
	使用 `Entity<?>`
		Nutz.Dao 会将你的 POJO 预先处理，处理的结果就是 `Entity<?>`。你可以通过 Dao 接口的 getEntity() 方法
		获取。你通过[annotations.man 实体注解]配置的信息，尤其是 @Column 中配置的数据库字段的名字
		（当数据库字段名同 Java 字段名不同时）尤其有用。Condition 接口的 `toSql(Entity<?>)` 方法是你唯一
		要实现的方法，如果你将一个 Condition 赋个了 Sql 对象，在生成真正 SQL 语句的时候，这个 `Entity<?>`
		又要从那里来呢？答案是，(*你要预先设置}。
		
		如果你不设置 `Entity<?>`，那么你的 Condition 的 `toSql(Entity<?>)` 参数就是 null。你可以通过 Dao
		接口随时获取任何一个 POJO 的`Entity<?>`，但是如何设置给你的 Condition 呢，答案是，通过 Sql 对象。
		{{{<JAVA>
		void demoEntityCondition(Dao dao) {
			Sql sql = Sqls.create("UPDATE t_pet SET masterid=@masterId $condition");
			Entity<Pet> entity = dao.getEntity(Pet.class);
			sql.setEntity(entity).setCondition(new Condition() {
				public String toSql(Entity<?> entity) {
					return String.format("%s LIKE 'Y%'", entity.getField("name").getColumnName());
				}
			});
			dao.execute(sql);
		}
		}}}
		很多时候，大量的 SQL 语句就是为了能够查出一些 POJO 对象，因此，我给你内置了两个回调，这两个回调都需要你
		为你的 Sql 设置一个正确的 `Entity<?>`
		
		获取实体的回调
			{{{<JAVA>
			Pet demoEntityQuery(Dao dao) {
				Sql sql = Sqls.create("SELECT * FROM t_pet $condition");
				sql.setCallback(Sqls.callback.entity());
				Entity<Pet> entity = dao.getEntity(Pet.class);
				sql.setEntity(entity).setCondition(Cnd.wrap("id=15"));
				dao.execute(sql);
				return sql.getObject(Pet.class);
			}
			}}}
			为了方便起见，你可以直接使用 Sqls.fetch 来创建你的 Sql 对象，这个函数会自动为你的 Sql 设置获取实体的回调
			{{{<JAVA>
			Pet demoEntityQuery(Dao dao) {
				Sql sql = Sqls.fetchEntity("SELECT * FROM t_pet $condition");
				Entity<Pet> entity = dao.getEntity(Pet.class);
				sql.setEntity(entity).setCondition(Cnd.wrap("id=15"));
				dao.execute(sql);
				return sql.getObject(Pet.class);
			}
			}}}
			
		查询实体的回调
			{{{<JAVA>
			List<Pet> demoEntityQuery(Dao dao) {
				Sql sql = Sqls.create("SELECT * FROM t_pet $condition");
				sql.setCallback(Sqls.callback.entities());
				Entity<Pet> entity = dao.getEntity(Pet.class);
				sql.setEntity(entity).setCondition(Cnd.wrap("id=15"));
				dao.execute(sql);
				return sql.getList(Pet.class);
			}
			}}}
			
			那么，我提供了一个 Sqls.queryEntity 函数也就不奇怪了吧。 :)
			
	多条件占位符(1.b.53开始提供)
	
		任意变量的值类型为Condition时,均自动识别为条件占位符
		
			{{{<JAVA>
				Sql sql = Sqls.create("select * from user where id in (select id from vips $vip_cnd ) and name in (select name from girls $girl_cnd )");
				sql.setVar("vip_cnd", Cnd.where("level", ">", 5));
				sql.setVar("girl_cnd", Cnd.where("age", "<", 30));
				sql.setCallback(Sqls.callback.records());
				dao.execute(sql);
			}}}
			
		注意事项: 如果调用过setEntity,那么对应的Cnd会进行属性名-字段名映射.
	
------------------------------------------------------------------------------------------------
分页

	对于不太复杂的sql, 分页还是需要的.
	
		{{{<JAVA>
		Sql sql = Sqls.queryEntity("SELECT * FROM t_pet");
		sql.setPager(dao.createPager(2,20));
		sql.setEntity(dao.getEntity(Pet.class));
		dao.execute(sql);
		return sql.getList(Pet.class);
		}}}
		
	提醒一下, 用于分页的sql对象,不可重复使用!!
	
	若自动分页失败(例如生成的sql错误),那只能自行分页了.

------------------------------------------------------------------------------------------------
SQL内的数据库特殊注释

    注释不可用单独一行.例如下面的例子,让SELECT与注释同一行,即可避免被当成sql的key进行处理.

	{{{SQL
	/* user.create */

    /*+ALL+_ROWS*/ SELECT  
    EMP_NO,EMP_NAM,DAT_IN 
    FROM BSEMPMS 
    WHERE EMP_NO=’SCOTT’
	}}}

------------------------------------------------------------------------------------------------
安全性

    基本原则,不允许把不可信数据(例如从网页表单获取的参数值)直接拼入SQL
    
    
    
    错误示例
    {{{JAVA
    
    @At("/query")
    public List<User> query(String city) {
        Sql sql = Sqls.queryEntity("select * from t_user where city='" + city + "'"); // 随便就被注入了
        sql.setEntity(dao.getEntity(User.class));
        dao.execute(sql);
        return sql.getList(User.class);
    }
    }}}
    
    正确用法
    {{{JAVA
    
    @At("/query")
    public List<User> query(String city) {
        Sql sql = Sqls.queryEntity("select * from t_user where city=@city");//参数化
        sql.setEntity(dao.getEntity(User.class));
        sql.params().set("city", city);
        dao.execute(sql);
        return sql.getList(User.class);
    }
    }}}
    
    若需要拼入,那么请使用Sqls.escapeXXXXX系列方法,针对性地移除特殊字符,保障安全!!